<?php

namespace app\index\controller;

use think\Cache;
use think\Config;
use think\Controller;
use app\common\model\Customer;
use PHPExcel;


/*
 * ligang
 */

use app\common\model\ForCoreRecord;
use app\common\model\Contract;
use app\common\model\ContractEquipment;
use app\common\model\WorkOrder;
use think\Db;
use think\Exception;
use think\Session;


class Index extends Controller
{

    public function index()
    {
     //  echo "欢迎来到CRM系统，请登录!";return false;
     
//        session('p',999);
       return $this->fetch('index');
    }

    public function num(){
        $data = file_get_contents(ROOT_PATH . 'public' . DS .'personnel_number.txt');
        $data = explode(',',$data);
        dump($data);
        exit();
        $data = Db::table('rq_personnel_number')->field('personnel_number')->select();
        $tmp = [];
        foreach ($data as $key=>$value){
            array_push($tmp,$value['personnel_number']);
        }
        $d = implode(",", $tmp);
        $myfile = fopen(ROOT_PATH . 'public' . DS ."personnel_number.txt", "w");
        fwrite($myfile, $d);
        fclose($myfile);
        dump($d);
    }

    public function contract_fail()
    {
        $fail_user_data = session('fail_user_data');
        $fail_user_a_data = session('fail_user_a_data');
        $fail_contract_data = session('fail_contract_data');
        $fail_contract_remark_data = session('fail_contract_remark_data');
        $fail_c_eq_data = session('fail_c_eq_data');
        $fail_c_log = session('fail_c_log');

        $fail_user_data_1 = session('fail_user_data_1');
        $fail_user_a_data_1 = session('fail_user_a_data_1');
        $fail_contract_data_1 = session('fail_contract_data_1');
        $fail_contract_remark_data_1 = session('fail_contract_remark_data_1');
        $fail_c_eq_data_1 = session('fail_c_eq_data_1');
        $fail_c_log_1 = session('fail_c_log_1');

        $fail_user_data_2 = session('fail_user_data_2');
        $fail_user_a_data_2 = session('fail_user_a_data_2');
        $fail_contract_data_2 = session('fail_contract_data_2');
        $fail_contract_remark_data_2 = session('fail_contract_remark_data_2');
        $fail_c_eq_data_2 = session('fail_c_eq_data_2');
        $fail_c_log_2 = session('fail_c_log_2');

        $fail_user_data_3 = session('fail_user_data_3');
        $fail_user_a_data_3 = session('fail_user_a_data_3');
        $fail_contract_data_3 = session('fail_contract_data_3');
        $fail_contract_remark_data_3 = session('fail_contract_remark_data_3');
        $fail_c_eq_data_3 = session('fail_c_eq_data_3');
        $fail_c_log_3 = session('fail_c_log_3');

        $fail_user_data = array_merge($fail_user_data,$fail_user_data_1,$fail_user_data_2,$fail_user_data_3);
        $fail_user_a_data = array_merge($fail_user_a_data,$fail_user_a_data_1,$fail_user_a_data_2,$fail_user_a_data_3);
        $fail_contract_data = array_merge($fail_contract_data,$fail_contract_data_1,$fail_contract_data_2,$fail_contract_data_3);
        $fail_contract_remark_data = array_merge($fail_contract_remark_data,$fail_contract_remark_data_1,$fail_contract_remark_data_2,$fail_contract_remark_data_3);
        $fail_c_eq_data = array_merge($fail_c_eq_data,$fail_c_eq_data_1,$fail_c_eq_data_2,$fail_c_eq_data_3);
        $fail_c_log = array_merge($fail_c_log,$fail_c_log_1,$fail_c_log_2,$fail_c_log_3);
        $fail_contract_data[0]['introducer_id'] = '1700105';

        $data = [
            'fail_user_data'=>$fail_user_data,
            'fail_user_a_data'=>$fail_user_a_data,
            'fail_contract_data'=>$fail_contract_data,
            'fail_contract_remark_data'=>$fail_contract_remark_data,
            'fail_c_eq_data'=>$fail_c_eq_data,
            'fail_c_log'=>$fail_c_log,
        ];
        session('fail',$data);
        echo "ok";
        exit();
        Db::startTrans();
        try{
            foreach ($fail_user_data as $uk=>$uv){
                $check_user = Db::name('customer')->where(['username'=>$uv['username'],'id_card'=>$uv['id_card']])->find();
                if (!empty($check_user)){
                    unset($fail_user_data[$uk]);
                    foreach ($fail_user_a_data as $uak=>$uav){
                        if ($uav['user_id'] == $uv['user_id']){
                            unset($fail_user_a_data[$uak]);
                        }
                    }
                }
            }
            Db::table('rq_contract')->insertAll($fail_contract_data);
            Db::table('rq_customer')->insertAll($fail_user_data);
            Db::table('rq_customer_administrative_center')->insertAll($fail_user_a_data);
            Db::table('rq_contract_remark')->insertAll($fail_contract_remark_data);
            Db::table('rq_contract_equipment')->insertAll($fail_c_eq_data);
            Db::table('rq_contract_log')->insertAll($fail_c_log);
            // 提交事务
            echo "--------------------------<br>";
            echo date('Y-m-d H:i:s',time())."<br>";
            echo "成功";
            Db::commit();
        } catch (\Exception $e) {
            echo $e;
            // 回滚事务
            Db::rollback();
        }

    }

    public function fail()
    {
        $a = 10+5+-5;
        dump($a);
        exit();
        /*
        $data = session('fail');

        $fail_user_data_tmp = [];
        foreach ($data['fail_user_data'] as $key=>$value){
            if (in_array($value['user_id'],$fail_user_data_tmp)){
                unset($data['fail_user_data'][$key]);
            }
            array_push($fail_user_data_tmp,$value['user_id']);
        }

        $fail_user_a_data_tmp = [];
        foreach ($data['fail_user_a_data'] as $key=>$value){
            if (in_array($value['user_id'],$fail_user_a_data_tmp)){
                unset($data['fail_user_a_data'][$key]);
            }
            array_push($fail_user_a_data_tmp,$value['user_id']);
        }

        $fail_contract_data_tmp = [];
        foreach ($data['fail_contract_data'] as $key=>$value){
            if (in_array($value['contract_no'],$fail_contract_data_tmp)){
                unset($data['fail_contract_data'][$key]);
            }
            array_push($fail_contract_data_tmp,$value['contract_no']);
        }

        $fail_contract_remark_data_tmp = [];
        foreach ($data['fail_contract_remark_data'] as $key=>$value){
            if (in_array($value['contract_no'],$fail_contract_remark_data_tmp)){
                unset($data['fail_contract_remark_data'][$key]);
            }
            array_push($fail_contract_remark_data_tmp,$value['contract_no']);
        }

        $fail_c_eq_data_tmp = [];
        foreach ($data['fail_c_eq_data'] as $key=>$value){
            if (in_array($value['contract_id'],$fail_c_eq_data_tmp)){
                unset($data['fail_c_eq_data'][$key]);
            }
            array_push($fail_c_eq_data_tmp,$value['contract_id']);
        }

        $fail_c_log_tmp = [];
        foreach ($data['fail_c_log'] as $key=>$value){
            if (in_array($value['contract_id'],$fail_c_log_tmp)){
                unset($data['fail_c_log'][$key]);
            }
            array_push($fail_c_log_tmp,$value['contract_id']);
        }
        \session('fail',$data);
        dump($data);
        exit();

*/
        /*
        $data = session('fail');
        $id = input('get.id') ?? '';
        $uid = input('get.uid') ?? '';
        if (empty($id)){
            echo "id";
        }else{
            foreach ($data['fail_contract_data'] as $key=>$value){
                if ($value['contract_id'] == $id){
                    $contract_id = Db::name('contract')->max('contract_id') + 4;
                    foreach ($data['fail_c_eq_data'] as $k=>$v){
                        if ($v['contract_id'] == $value['contract_id']){
                            $data['fail_c_eq_data'][$k]['contract_id'] = $contract_id;
                        }
                    }
                    foreach ($data['fail_c_log'] as $kk=>$vv){
                        if ($vv['contract_id'] == $value['contract_id']){
                            $data['fail_c_log'][$kk]['contract_id'] = $contract_id;
                        }
                    }
                    $data['fail_contract_data'][$key]['contract_id'] = $contract_id;
                }
            }
        }

        if (empty($uid)){
            echo "uid";
        }else{
            foreach ($data['fail_user_data'] as $key=>$value){
                if ($value['user_id'] == $uid){
                    $user_id = Db::name('customer')->max('user_id') + 4;
                    foreach ($data['fail_user_a_data'] as $uak=>$uav){
                        if ($uav['user_id'] == $value['user_id']){
                            $data['fail_user_a_data'][$uak]['user_id'] = $user_id;
                        }
                    }
                    foreach ($data['fail_contract_data'] as $cdk=>$cdv){
                        if ($cdv['user_id'] == $value['user_id']){
                            $data['fail_contract_data'][$cdk]['user_id'] = $user_id;
                        }
                    }
                    $data['fail_user_data'][$key]['user_id'] = $user_id;
                }
            }
        }


        dump($data);
        \session('fail',$data);
        exit();
*/

//        $data = session('fail');
//
//        $data['fail_contract_data'][12]['contract_id'] = '1158';
//        \session('fail',$data);

//        dump(session('fail'));

        /*
        $file=ROOT_PATH . 'public' . DS . 'customer' . DS . "fail.php";
//缓存
        $text='<?php $rows='.var_export($data,true).';';
        if(false!==fopen($file,'w+')){
            file_put_contents($file,$text);
        }else{
            echo '创建失败';
        }
        dump($data);
        */
//        $rows = require_once(ROOT_PATH . 'public' . DS . 'customer' . DS . 'fail.php');
//
//        $data = $rows;
//
//
//
//        \session('fail',$data);
//        dump($data);
//        $data = session('fail');
//
//        foreach ($data['fail_contract_data'] as $key=>$value){
//            if (strlen($value['introducer_id']) == 1){
//                $data['fail_contract_data'][$key]['introducer_id'] = '';
//            }
//        }
//        session('fail',$data);
//        dump($data);
//        $data = session('fail');
//        $data['fail_contract_data'][6]['introducer_id'] = '1609736';
//        $data['fail_contract_data'][12]['introducer_id'] = '0042068';
//        $data['fail_contract_data'][14]['introducer_id'] = '0042068';
//        $data['fail_contract_data'][11]['receipt'] = '0014150';
//        $data['fail_contract_data'][11]['contract_money'] = '1680';
//        $data['fail_contract_data'][11]['address'] = '重庆市渝北区空港中学浩博天地6栋7-3';
//
//        $data['fail_contract_remark_data'][11]['contract_no'] = '0042068';
//        $data['fail_contract_remark_data'][11]['remark'] = '';
//
//        $data['fail_c_log'][11]['new_value'] = '合同，合同编号：0042068';
//
//        $data['fail_c_eq_data'][11]['equipments_address'] = '重庆市渝北区空港中学浩博天地6栋7-3';
//        dump($data);
//        session('fail',$data);
        dump(session('fail'));
    }

    /**
     *
     * 请求参数     请求类型      是否必须      说明
     * @author ligang
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @date time
     */
    public function audit()
    {
        exit();
        $Model = new WorkOrder();
        $all = Db::table('rq_work_order')->where(['work_order_status'=>['<>',12]])->field('work_order_id,audit_status')->select();
        foreach ($all as $k=>&$v){
            $v['audit_status'] = 3;
        }
        $a = $Model->saveAll($all);
        dump($a);
    }

    public function doFail()
    {
        exit();
        $data = session('fail');
        Db::startTrans();
        try {
        //排除相同的人

//            dump($data['fail_user_data']);
//            exit();
        foreach ($data['fail_user_data'] as $k=>$v){
            $result = Db::name('customer')->where(['username'=>$v['username'],'id_card'=>$v['id_card']])->find();
            if (!empty($result)){
                unset($data['fail_user_data'][$k]);
                //同时删除行政中心关联数据
                foreach ($data['fail_user_a_data'] as $kk=>$vv){
                    if ($v['user_id'] == $vv['user_id']){
                        unset($data['fail_user_a_data'][$k]);
                    }
                }
            }
        }

        $tmp_c = [];
        $tmp_r = [];
        $tmp_e = [];
        $tmp_l = [];
        $repeat = [];
        //寻找转介绍人

        $data['fail_contract_remark_data'][2]['remark'] .= $data['fail_contract_data'][2]['introducer_id'];
        $data['fail_contract_data'][2]['introducer_id'] = '';

        $data['fail_contract_remark_data'][3]['remark'] .= $data['fail_contract_data'][3]['introducer_id'];
        $data['fail_contract_data'][3]['introducer_id'] = '';

        foreach ($data['fail_contract_data'] as $kk=>$vv){
            if ($kk >= 4 && $kk <= 27){
                unset($data['fail_contract_data'][$kk]);
            }
        }

        foreach ($data['fail_contract_data'] as $k=>$v){

            $v['contract_no'] = trim($v['contract_no']);
            $v['introducer_id'] = trim($v['introducer_id']);

            if ($v['administrative_id'] == 15){
                $data['fail_contract_data'][$k]['applicant'] = $v['introducer_id'];
                $data['fail_contract_data'][$k]['introducer_id'] = '';
                $v['introducer_id'] = '';
            }



            if (in_array($v['contract_no'],session('contract_no'))){
                array_push($repeat,$v);
                unset($data[$k]);
                continue;
            }
            if (!empty($v['introducer_id'])){
                $c_result = $this->getContract($v['introducer_id']);
                if (!empty($c_result)){
                    $data['fail_contract_data'][$k]['introducer_id'] = $c_result;
                }else{
                    array_push($tmp_c,$v);
                    unset($data['fail_contract_data'][$k]);
                    //同时排除
                    foreach ($data['fail_contract_remark_data'] as $rk=>$rv){
                        if ($v['contract_no'] == $rv['contract_no']){
                            array_push($tmp_r,$rv);
                            unset($data['fail_contract_remark_data'][$rk]);
                        }
                    }
                    foreach ($data['fail_c_eq_data'] as $ek=>$ev){
                        if ($v['contract_id'] == $ev['contract_id']){
                            array_push($tmp_e,$ev);
                            unset($data['fail_c_eq_data'][$ek]);
                        }
                    }
                    foreach ($data['fail_c_log'] as $lk=>$lv){
                        if ($v['contract_id'] == $lv['contract_id']){
                            array_push($tmp_l,$lv);
                            unset($data['fail_c_eq_data'][$lk]);
                        }
                    }

                }
            }
        }
//        dump($data);
//        exit();
        $tmp_data = [
            'fail_user_data'=>[],
            'fail_user_a_data'=>[],
            'fail_contract_data'=>$tmp_c,
            'fail_contract_remark_data'=>$tmp_r,
            'fail_c_eq_data'=>$tmp_e,
            'fail_c_log'=>$tmp_l
        ];


            Db::table('rq_contract')->insertAll($data['fail_contract_data']);
            Db::table('rq_customer')->insertAll($data['fail_user_data']);
            Db::table('rq_customer_administrative_center')->insertAll($data['fail_user_a_data']);
            Db::table('rq_contract_remark')->insertAll($data['fail_contract_remark_data']);
            Db::table('rq_contract_equipment')->insertAll($data['fail_c_eq_data']);
            Db::table('rq_contract_log')->insertAll($data['fail_c_log']);
            // 提交事务
            echo "成功执行：".count($data['fail_contract_data'])."<br>";
            echo "--------------------------<br>";
            echo date('Y-m-d H:i:s',time())."<br>";
            echo "成功";
            session('fail',$tmp_data);
            session('repeat',$repeat);
            Db::commit();
        } catch (\Exception $e) {
            echo $e;
            // 回滚事务
            Db::rollback();
        }

    }

    public function contract_new()
    {
        exit();
        ob_end_clean();
        set_time_limit(0);
        ini_set('memory_limit', '3072M');
        ini_set("max_execution_time", "0");
        $file = ROOT_PATH . 'public' . DS . 'customer' . DS . 'new' . DS . '01.xlsx';
        $p = input('get.p/d');
        $c = input('get.c');
        $d = input('get.d');
        if ($d == 1000){
            session('p',9999);
        }
        if (empty($c)){
            echo "缺少参数";
            exit();
        }
        if ($c != '0523'){
            echo "参数错误";
            exit();
        }
        if ($p != 8) {
            echo "不能导入该sheet";
            exit();
        }
        if ($p == session('p')){
            echo "请勿重新导入";
            exit();
        }

        \session('p',$p);


        // 判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type = $type === 'Excel5' ? $type : 'Excel2007';

        Vendor('phpexcel.phpexcel.Classes.PHPExcel');

        // 判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);

        if ($p >= count($objPHPExcel->getSheetNames())){
            echo "没有更多sheet";
            exit();
        };

        //获取sheet
        $sheet = $objPHPExcel->getSheet($p);

        //获取sheet名
        $sheet_name = $objPHPExcel->getSheetNames()[$p];

        $a_info = $this->getAdministrative($sheet_name);
        if (empty($a_info)) {
            echo "该行政中心不存在";
            exit();
        }
        // 取得总行数
        $highestRow = $sheet->getHighestRow();
//        dump($highestRow);
//        dump($sheet->getCell('A2')->getValue());
//        dump(session('user_id'));
//        dump(session('c_id'));
//        exit();

        //用户数据
        $u_data = [
            'user_id' => '',
            'telphone' => '',//联系电话
            'id_card' => '',//身份证号码
            'username' => '',//客户姓名
            'avatar_img' => 0,//用户头像地址
            'province' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['province_name'],//省
            'city' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['city_name'],//市
            'area' => ($a_info['a_id'] == 32) ? '渝中区' : $a_info['area_name'],//区
            'province_code' => ($a_info['a_id'] == 32) ? 23 : $a_info['province_id'],//省ID
            'city_code' => ($a_info['a_id'] == 32) ? 271 : $a_info['city_id'],//市ID
            'area_code' => ($a_info['a_id'] == 32) ? 2494 : $a_info['area_id'],//区ID
            'number' => 0,//当前推荐有效设备数（直推设备台数）   !!!
            'team_number' => 0,//团队推荐台数（含直推）         !!!
            'user_address' => '',//客户详细地址
            'source' => 0,//客户来源(联系人ID) !!!
            'birthday' => '',//生日
            'operation_id' => $a_info['operation'],//运营中心ID
            'nature' => 2,//客户性质(1.商用客户,2.家用客户,3外接包年,4.外接点餐,5.购买客户)
            'housing_property' => 4,//住房性质(1.公租房,2.廉租房,3.租赁,4.购买)
            'is_entry' => 2,//是否入职(1.入职，3.离职，2.客户)
            'user_type' => 1,//用户类型(1.普通用户,2.vip用户) 商用客户or家用客户必选
            'remark' => '',//帐号备注
            'employee_id' => '',//员工编号
            'add_time' => time(),//注册时间
        ];

        //行政中心数据
        $u_a_data = [
            'user_id' => '',
            'administrative_id' => $a_info['a_id']
        ];

        //合同数据
        $contract = [
            'contract_id' => '合同ID',
            'user_id' => '用户ID',
            'introducer_id' => '',
            'applicant' => '申请人',
            'administrative_id' => $a_info['a_id'],
            'operation_id' => $a_info['operation'],
            'province' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['province_name'],//省
            'city' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['city_name'],//市
            'area' => ($a_info['a_id'] == 32) ? '渝中区' : $a_info['area_name'],//区
            'province_code' => ($a_info['a_id'] == 32) ? 23 : $a_info['province_id'],//省ID
            'city_code' => ($a_info['a_id'] == 32) ? 271 : $a_info['city_id'],//市ID
            'area_code' => ($a_info['a_id'] == 32) ? 2494 : $a_info['area_id'],//区ID
            'contract_no' => '合同编号',
            'water_pressure' => '水压力',
            'receipt' => '收据编号',
            'contract_money' => '合同金额',
            'phone' => '联系电话',
            'contact_person' => '联系人',
            'corporate_name' => '公司名称',
            'installed_time' => '装机时间',
            'address' => '装机地址',
            'for_core_cost' => '',
            'for_core_cycle' => '换芯周期(月)',
            'for_core_time' => '换芯时间（初次装机填写装机时间）',
            'recent_time' => '最近一次换芯时间',
            'exire_date' => '合同到期时间',
            'renew_date' => '续费周期',
            'contract_status' => 1,
            'add_time' => time(),
            'nature' => '客户性质(1.商用客户,2.家用客户,3外接包年,4.外接点餐,5.购买客户)',
            'housing_property' => '住房性质(1.公租房,2.廉租房,3.租赁,4.购买)',
            'contract_type' => '是否为VIP 0:否 1：是',
            'is_explain' => '有无讲解，1：有，2：无',
        ];

        //合同备注
        $contract_remark = [
            'contract_no' => '合同编号',
            'remark' => '备注内容',
            'add_time' => time()
        ];

        //合同设备
        $c_eq = [
            'contract_id' => '合同ID',
            'equipments_id' => '设备ID',
            'equipments_number' => '',
            'equipments_address' => '设备地址',
            'state' => 1,
            'addtime' => time(),
            'uptime' => 0,
        ];

        $user_data = [];
        $user_a_data = [];
        $contract_data = [];
        $contract_remark_data = [];
        $c_eq_data = [];
        $c_log = [];

        //失败的数据
        $fail_user_data = [];
        $fail_user_a_data = [];
        $fail_contract_data = [];
        $fail_contract_remark_data = [];
        $fail_c_eq_data = [];
        $fail_c_log = [];

//        $user_id = 5301;//其实用户ID
        $user_id = session('user_id');//其实用户ID
//        $c_id = 2724;//其实合同ID
        $c_id = session('c_id');//其实合同ID
//        $user_id = Db::name('customer')->max('user_id') + 1;//其实用户ID
//        $c_id = Db::name('contract')->max('contract_id') + 1;//其实合同ID

        Db::startTrans();
        try {
        for ($i = 2; $i <= $highestRow; $i++) {
            //用户数据
            $u_data['user_id'] = $user_id;
            $u_data['telphone'] = substr($sheet->getCell('F' . $i)->getValue(), 0, 11);
            $u_data['id_card'] = $sheet->getCell('N' . $i)->getValue();
            $u_data['username'] = $sheet->getCell('A' . $i)->getValue();
            $u_data['user_address'] = $sheet->getCell('G' . $i)->getValue();
            $u_data['birthday'] = strtotime($sheet->getCell('M' . $i)->getValue()) ? strtotime($sheet->getCell('L' . $i)->getValue()) : time();

            switch ($sheet->getCell('B' . $i)->getValue()) {
                case '商用':
                    $nature = 1;
                    break;
                case '家用':
                    $nature = 2;
                    break;
                case '外接包年':
                    $nature = 3;
                    break;
                case '外接点餐':
                    $nature = 4;
                    break;
                case '购买客户':
                    $nature = 5;
                    break;
                default:
                    $nature = 2;
            }
            switch ($sheet->getCell('P' . $i)->getValue()) {
                case '公租房':
                    $housing_property = 1;
                    break;
                case '廉租房':
                    $housing_property = 2;
                    break;
                case '租赁':
                    $housing_property = 3;
                    break;
                default:
                    $housing_property = 4;
            }
            switch ($sheet->getCell('D' . $i)->getValue()) {
                case '普通客户':
                    $user_type = 1;
                    break;
                case 'VIP客户':
                    $user_type = 2;
                    break;
                default:
                    $user_type = 1;
            }

            $u_data['nature'] = $nature;
            $u_data['housing_property'] = $housing_property;
            $u_data['user_type'] = $user_type;

            $u_data['remark'] = substr($sheet->getCell('F' . $i)->getValue(), 11);

            //行政中心数据
            $u_a_data['user_id'] = $user_id;

            //合同
            $contract['contract_id'] = $c_id;
            $contract['user_id'] = $user_id;
            $contract['applicant'] = $this->getWorker($sheet->getCell('T' . $i)->getValue());
            $contract['introducer_id'] = $this->getContract($sheet->getCell('U' . $i)->getValue());

            $contract['contract_no'] = trim($sheet->getCell('C' . $i)->getValue());
            $contract['water_pressure'] = $sheet->getCell('H' . $i)->getValue();
            $contract['receipt'] = $sheet->getCell('V' . $i)->getValue();
//            $contract['contract_money'] = $this->findNum($sheet->getCell('P' . $i)->getValue());
            $contract['contract_money'] = 3980;
            $contract['phone'] = substr($sheet->getCell('F' . $i)->getValue(), 0, 11);
            $contract['contact_person'] = $sheet->getCell('A' . $i)->getValue();

            $contract['corporate_name'] = $sheet->getCell('A' . $i)->getValue();

            $contract['installed_time'] = strtotime($sheet->getCell('W' . $i)->getValue()) ? strtotime($sheet->getCell('W' . $i)->getValue()) : time();
            $contract['address'] = $sheet->getCell('G' . $i)->getValue();
            $contract['for_core_cycle'] = $this->findNum($sheet->getCell('O' . $i)->getValue());
//            $contract['recent_time'] = strtotime($sheet->getCell('Q' . $i)->getValue()) ? strtotime($sheet->getCell('Q' . $i)->getValue()) : time();
            $contract['recent_time'] = $contract['installed_time'];

            $contract['for_core_time'] = strtotime('+' . $contract['for_core_cycle'] . 'month', $contract['recent_time']);
            $contract['for_core_cost'] = $this->findNum($sheet->getCell('R' . $i)->getValue());

//            $contract['renew_date'] = $sheet->getCell('R' . $i)->getValue() ?? 1;
            $contract['renew_date'] = !empty($sheet->getCell('S' . $i)->getValue())?$sheet->getCell('S' . $i)->getValue():1;
//            $contract['exire_date'] = strtotime("+1year", strtotime($sheet->getCell('R' . $i)->getValue()));
            $contract['exire_date'] = strtotime("+{$contract['renew_date']}year", $contract['installed_time']);

            $contract['nature'] = $nature;
            $contract['housing_property'] = $housing_property;
            $contract['contract_type'] = $user_type - 1;
            $contract['is_explain'] = (empty($sheet->getCell('X' . $i)->getValue()) || $sheet->getCell('X' . $i)->getValue() == '无') ? 2 : 1;

            //合同备注
            $contract_remark['contract_no'] = trim($sheet->getCell('C' . $i)->getValue());
            $contract_remark['remark'] = $sheet->getCell('Y' . $i)->getValue();

            //合同设备
            $c_eq['contract_id'] = $c_id;
//            dump($sheet->getCell('P' . $i)->getValue());
            $c_eq['equipments_id'] = $this->getEq($sheet->getCell('Q' . $i)->getValue());
            $c_eq['equipments_number'] = randEquipmentsNumber();
            $c_eq['equipments_address'] = $contract['address'];

            //合同日志
            $Log = [
                'contract_id' => $c_id,
                'do_type' => 1,
                'terminal_type' => 1,
                'new_value' => '合同，合同编号：' . $contract_remark['contract_no'],
            ];

            //如果存在转介绍且转介绍不为空，则将数据添加到数据库，反之则存入缓存
            if ((in_array($contract['contract_no'],session('contract_no')) || in_array($u_data['username'],session('username'))) || (!empty($sheet->getCell('U' . $i)->getValue()) && empty($contract['introducer_id']))){
                $contract['introducer_id'] = $sheet->getCell('U' . $i)->getValue();
                array_push($fail_user_data, $u_data);
                array_push($fail_user_a_data, $u_a_data);
                array_push($fail_contract_data, $contract);
                array_push($fail_contract_remark_data, $contract_remark);
                array_push($fail_c_eq_data, $c_eq);
                array_push($fail_c_log, $Log);
            }else{
                array_push($user_data, $u_data);
                array_push($user_a_data, $u_a_data);
                array_push($contract_data, $contract);
                array_push($contract_remark_data, $contract_remark);
                array_push($c_eq_data, $c_eq);
                array_push($c_log, $Log);
            }
            $user_id++;
            $c_id++;
        }

//        if (!empty($fail_contract_data)){
////            echo "寻找转介绍失败：".count($fail_contract_data).'<br>';
//            $data = session('fail');
//            $data['fail_user_data'] = array_merge($data['fail_user_data'],$fail_user_data);
//            $data['fail_user_a_data'] = array_merge($data['fail_user_a_data'],$fail_user_a_data);
//            $data['fail_contract_data'] = array_merge($data['fail_contract_data'],$fail_contract_data);
//            $data['fail_contract_remark_data'] = array_merge($data['fail_contract_remark_data'],$fail_contract_remark_data);
//            $data['fail_c_eq_data'] = array_merge($data['fail_c_eq_data'],$fail_c_eq_data);
//            $data['fail_c_log'] = array_merge($data['fail_c_log'],$fail_c_log);
//            session('fail',$data);
//        }

        $tmp_user_name = [];
        foreach ($user_data as $uk=>$uv){
            if (in_array($uv['username'],$tmp_user_name)){
                unset($user_data[$uk]);
            }else{
                array_push($tmp_user_name,$uv['username']);
            }
            if (empty($uv['username'])){
                unset($user_data[$uk]);
                foreach ($user_a_data as $uak=>$uav){
                    if ($uav['user_id'] == $uv['user_id']){
                        unset($user_a_data[$uak]);
                    }
                }
            }
        }

        foreach ($contract_data as $ck=>$cv){
            if (empty($cv['contract_no'])){
                unset($contract_data[$ck]);
                foreach ($c_log as $lk=>$lv){
                    if ($lv['contract_id'] == $cv['contract_id']){
                        unset($c_log[$lk]);
                    }
                }
            }
        }

        foreach ($contract_remark_data as $crk=>$crv){
            if (empty($crv['contract_no'])){
                unset($contract_remark_data[$crk]);
            }
        }
            Db::table('rq_contract')->insertAll($contract_data);
            Db::table('rq_customer')->insertAll($user_data);
            Db::table('rq_customer_administrative_center')->insertAll($user_a_data);
            Db::table('rq_contract_remark')->insertAll($contract_remark_data);
            Db::table('rq_contract_equipment')->insertAll($c_eq_data);
            Db::table('rq_contract_log')->insertAll($c_log);
            // 提交事务
            echo "--------------------------<br>";
            echo date('Y-m-d H:i:s',time())."<br>";
            echo "成功<br>";
            echo "当前页数：".$p.'<br>';
            echo "总计：".($highestRow - 1).'<br>';
            echo '成功执行：'.count($user_data).'<br>';
            echo '失败执行：'.($highestRow - 1 - count($user_data)).'<br>';


        session('user_id',$user_id);//其实用户ID
        session('c_id',$c_id);//其实合同ID

            Db::commit();
            $this->people();
        } catch (\Exception $e) {
            echo $e;
            // 回滚事务
            Db::rollback();
        }
    }

    public function people()
    {
        $data = Db::table('rq_customer')->field('rq_customer.username,rq_customer.id_card,rq_customer.telphone')->select();
        $tmp_name = [];
        $tmp_card = [];
        $tmp_tel = [];
        foreach ($data as $key=>$value){
            array_push($tmp_name,trim($value['username']));
            array_push($tmp_card,trim($value['id_card']));
            array_push($tmp_tel,trim($value['telphone']));
        }
        \session('username',$tmp_name);
//        \session('id_card',$tmp_card);
//        \session('telphone',$tmp_tel);
//        dump($tmp_name);
//        exit();
        $data = Db::table('rq_contract')->field('contract_no')->select();
        $tmp = [];
        foreach ($data as $key=>$value){
            array_push($tmp,trim($value['contract_no']));
        }
        \session('contract_no',$tmp);
        dump(count($tmp));
        exit();
        $repeat_people = Db::name('customer_administrative_center')->field('user_id,Count(*) AS count')->group('user_id')->having('count>1')->select();
        dump($repeat_people);
        exit();
        foreach ($repeat_people as $key=>$value){
            $repeat_people_info = Db::name('customer_administrative_center')->where(['user_id'=>$value['user_id']])->select();
            if (count($repeat_people_info) > 1){
                foreach ($repeat_people_info as $k=>$v){
                    if ($k != 0){
                        Db::name('customer_administrative_center')->where(['id'=>$v['id']])->delete();
                    }
                }
            }
        }
        echo "ok";
        exit();
        ini_set("max_execution_time", "0");
        ob_end_clean();//清除缓冲区,避免乱码
        ob_start();
        header('Content-Type:text/html;charset=utf-8');
        try {
            $join = [
                ['rq_customer', 'rq_customer.user_id = rq_contract.user_id', 'LEFT'],
            ];
//            $data = Db::name('customer')->field($field)->join($join)->select();

            $repeat_people = Db::name('contract')->field('contract_no,Count(*) AS count')->group('contract_no')->having('count>1')->select();
            $data = [];
            foreach ($repeat_people as $key=>$value){
                $repeat_people_info = Db::name('contract')->join($join)->where(['contract_no'=>$value['contract_no']])->select();
                foreach ($repeat_people_info as $k=>$v){
                    array_push($data,$v);
                }
            }

            $filename = '合同编号重复信息' . '-' . date('Y-m-d H:i:s', time()) . ".xls";
            Vendor('phpexcel.phpexcel.Classes.PHPExcel');
            $PHPExcel = new \PHPExcel();
            //设置信任
            $PHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");
            //设置表头
            $PHPExcel->getActiveSheet()->setCellValue('A1', '合同ID');
            $PHPExcel->getActiveSheet()->setCellValue('B1', '客户姓名');
            $PHPExcel->getActiveSheet()->setCellValue('C1', '身份证');
            $PHPExcel->getActiveSheet()->setCellValue('D1', '合同编号');
            $PHPExcel->getActiveSheet()->setCellValue('E1', '合同地址');
            $PHPExcel->getActiveSheet()->setCellValue('F1', '收据编号');

            //设置列宽
            $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
            $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
            $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(28);
            $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(60);
            $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
            //设置字体样式
            $PHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setName('微软雅黑');
            $PHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setSize(14);
            $PHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);


            foreach ($data as $k => $v) {
                //设置单元格值
                $PHPExcel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['contract_id']);
                $PHPExcel->getActiveSheet()->setCellValue('B' . ($k + 2), $v['username']);
                $PHPExcel->getActiveSheet()->setCellValue('C' . ($k + 2), ' '.$v["id_card"]);
                $PHPExcel->getActiveSheet()->setCellValue('D' . ($k + 2), ' '.$v['contract_no']);
                $PHPExcel->getActiveSheet()->setCellValue('E' . ($k + 2), $v['address']);
                $PHPExcel->getActiveSheet()->setCellValue('F' . ($k + 2), $v['receipt']);


                //设置居中
                $PHPExcel->getActiveSheet()->getStyle('A1:F' . ($k + 2))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //垂直居中
                $PHPExcel->getActiveSheet()->getStyle('A' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('B' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('C' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('D' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('E' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('F' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            }
            $PHPSheet = $PHPExcel->getActiveSheet();
            //给当前活动sheet设置名称
            $PHPSheet->setTitle("合同编号重复信息");
            //创建生成的格式
            $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel5");
            //下载下来的表格名
            ob_end_clean();
            header('content-Type:application/vnd.ms-excel;charset=utf-8');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Cache-Control: max-age=0');
            $PHPWriter->save("php://output");
        } catch (\Exception $e) {
            echo $e;
        }
    }

    public function getSession()
    {
//        $data = [];
//        $data['fail_user_data'] = [];
//        $data['fail_user_a_data'] = [];
//        $data['fail_contract_data'] = [];
//        $data['fail_contract_remark_data'] = [];
//        $data['fail_c_eq_data'] = [];
//        $data['fail_c_log'] = [];
//        session('fail',$data);
        session('repeat');
        dump(session('fail'));
    }

    public function contract()
    {
        exit();
        ob_end_clean();
        set_time_limit(0);
        ini_set('memory_limit', '3072M');
        ini_set("max_execution_time", "0");
        $file = ROOT_PATH . 'public' . DS . 'customer' . DS . 'contract_4.xlsx';
        $p = input('get.p/d');
        $c = input('get.c');
        $d = input('get.d');
        if ($d == 1000){
            session('p',999);
        }
        if (empty($c)){
            echo "缺少参数";
            exit();
        }
        if ($c != '0523'){
            echo "参数错误";
            exit();
        }
//        if ($p == 0) {
//            echo "不能导入该sheet";
//            exit();
//        }

        if ($p == session('p')){
            echo "请勿重新导入";
            exit();
        }

        session('p',$p);



        // 判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type = $type === 'Excel5' ? $type : 'Excel2007';

        Vendor('phpexcel.phpexcel.Classes.PHPExcel');

        // 判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);

        if ($p >= count($objPHPExcel->getSheetNames())){
            echo "没有更多sheet";
            exit();
        };

        //获取sheet名
        $sheet_name = $objPHPExcel->getSheetNames()[$p];

        //获取sheet
        $sheet = $objPHPExcel->getSheet($p);

        $a_info = $this->getAdministrative($sheet_name);
        if (empty($a_info)) {
            echo "该行政中心不存在";
            exit();
        }
        // 取得总行数
        $highestRow = $sheet->getHighestRow();
//        dump($highestRow);
//        dump($sheet->getCell('A2')->getValue());
//        dump(session('user_id'));
//        dump(session('c_id'));
//        exit();

        //用户数据
        $u_data = [
            'user_id' => '',
            'telphone' => '',//联系电话
            'id_card' => '',//身份证号码
            'username' => '',//客户姓名
            'avatar_img' => 0,//用户头像地址
            'province' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['province_name'],//省
            'city' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['city_name'],//市
            'area' => ($a_info['a_id'] == 32) ? '渝中区' : $a_info['area_name'],//区
            'province_code' => ($a_info['a_id'] == 32) ? 23 : $a_info['province_id'],//省ID
            'city_code' => ($a_info['a_id'] == 32) ? 271 : $a_info['city_id'],//市ID
            'area_code' => ($a_info['a_id'] == 32) ? 2494 : $a_info['area_id'],//区ID
            'number' => 0,//当前推荐有效设备数（直推设备台数）   !!!
            'team_number' => 0,//团队推荐台数（含直推）         !!!
            'user_address' => '',//客户详细地址
            'source' => 0,//客户来源(联系人ID) !!!
            'birthday' => '',//生日
            'operation_id' => $a_info['operation'],//运营中心ID
            'nature' => 2,//客户性质(1.商用客户,2.家用客户,3外接包年,4.外接点餐,5.购买客户)
            'housing_property' => 4,//住房性质(1.公租房,2.廉租房,3.租赁,4.购买)
            'is_entry' => 2,//是否入职(1.入职，3.离职，2.客户)
            'user_type' => 1,//用户类型(1.普通用户,2.vip用户) 商用客户or家用客户必选
            'remark' => '',//帐号备注
            'employee_id' => '',//员工编号
            'add_time' => time(),//注册时间
        ];

        //行政中心数据
        $u_a_data = [
            'user_id' => '',
            'administrative_id' => $a_info['a_id']
        ];

        //合同数据
        $contract = [
            'contract_id' => '合同ID',
            'user_id' => '用户ID',
            'introducer_id' => '',
            'applicant' => '申请人',
            'administrative_id' => $a_info['a_id'],
            'operation_id' => $a_info['operation'],
            'province' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['province_name'],//省
            'city' => ($a_info['a_id'] == 32) ? '重庆市' : $a_info['city_name'],//市
            'area' => ($a_info['a_id'] == 32) ? '渝中区' : $a_info['area_name'],//区
            'province_code' => ($a_info['a_id'] == 32) ? 23 : $a_info['province_id'],//省ID
            'city_code' => ($a_info['a_id'] == 32) ? 271 : $a_info['city_id'],//市ID
            'area_code' => ($a_info['a_id'] == 32) ? 2494 : $a_info['area_id'],//区ID
            'contract_no' => '合同编号',
            'water_pressure' => '水压力',
            'receipt' => '收据编号',
            'contract_money' => '合同金额',
            'phone' => '联系电话',
            'contact_person' => '联系人',
            'corporate_name' => '公司名称',
            'installed_time' => '装机时间',
            'address' => '装机地址',
            'for_core_cost' => '',
            'for_core_cycle' => '换芯周期(月)',
            'for_core_time' => '换芯时间（初次装机填写装机时间）',
            'recent_time' => '最近一次换芯时间',
            'exire_date' => '合同到期时间',
            'renew_date' => '续费周期',
            'contract_status' => 1,
            'add_time' => time(),
            'nature' => '客户性质(1.商用客户,2.家用客户,3外接包年,4.外接点餐,5.购买客户)',
            'housing_property' => '住房性质(1.公租房,2.廉租房,3.租赁,4.购买)',
            'contract_type' => '是否为VIP 0:否 1：是',
            'is_explain' => '有无讲解，1：有，2：无',
        ];

        //合同备注
        $contract_remark = [
            'contract_no' => '合同编号',
            'remark' => '备注内容',
            'add_time' => time()
        ];

        //合同设备
        $c_eq = [
            'contract_id' => '合同ID',
            'equipments_id' => '设备ID',
            'equipments_number' => '',
            'equipments_address' => '设备地址',
            'state' => 1,
            'addtime' => time(),
            'uptime' => 0,
        ];

        $user_data = [];
        $user_a_data = [];
        $contract_data = [];
        $contract_remark_data = [];
        $c_eq_data = [];
        $c_log = [];

//        $user_id = ($p == 1) ? 2576 : session('user_id');//其实用户ID
//        $c_id = ($p == 1) ? 1 : session('c_id');//其实合同ID
        $user_id = Db::name('customer')->max('user_id') + 1;//其实用户ID
        $c_id = Db::name('contract')->max('contract_id') + 1;//其实合同ID
        $error = [];
        $error_user = [];
        for ($i = 2; $i <= $highestRow; $i++) {
            //用户数据
            $u_data['user_id'] = $user_id;
            $u_data['telphone'] = substr($sheet->getCell('F' . $i)->getValue(), 0, 11);
            $u_data['id_card'] = $sheet->getCell('I' . $i)->getValue();
            $u_data['username'] = $sheet->getCell('A' . $i)->getValue();
            $u_data['user_address'] = $sheet->getCell('G' . $i)->getValue();
            $u_data['birthday'] = strtotime($sheet->getCell('H' . $i)->getValue()) ? strtotime($sheet->getCell('H' . $i)->getValue()) : time();

            switch ($sheet->getCell('B' . $i)->getValue()) {
                case '商用':
                    $nature = 1;
                    break;
                case '家用':
                    $nature = 2;
                    break;
                case '外接包年':
                    $nature = 3;
                    break;
                case '外接点餐':
                    $nature = 4;
                    break;
                case '购买客户':
                    $nature = 5;
                    break;
                default:
                    $nature = 2;
            }
            switch ($sheet->getCell('J' . $i)->getValue()) {
                case '公租房':
                    $housing_property = 1;
                    break;
                case '廉租房':
                    $housing_property = 2;
                    break;
                case '租赁':
                    $housing_property = 3;
                    break;
                default:
                    $housing_property = 4;
            }
            switch ($sheet->getCell('D' . $i)->getValue()) {
                case '普通客户':
                    $user_type = 1;
                    break;
                case 'VIP客户':
                    $user_type = 2;
                    break;
                default:
                    $user_type = 1;
            }

            $u_data['nature'] = $nature;
            $u_data['housing_property'] = $housing_property;
            $u_data['user_type'] = $user_type;

            $u_data['remark'] = substr($sheet->getCell('F' . $i)->getValue(), 11);



            //行政中心数据
            $u_a_data['user_id'] = $user_id;


            //合同
            $contract['contract_id'] = $c_id;
            $contract['user_id'] = $user_id;
//            $contract['user_id'] = 475;
            $contract['applicant'] = $this->getWorker($sheet->getCell('V' . $i)->getValue());
            $contract['contract_no'] = trim($sheet->getCell('C' . $i)->getValue());
            $contract['water_pressure'] = $sheet->getCell('S' . $i)->getValue();
            $contract['receipt'] = $sheet->getCell('R' . $i)->getValue();
            $contract['contract_money'] = $this->findNum($sheet->getCell('O' . $i)->getValue());
            $contract['phone'] = substr($sheet->getCell('F' . $i)->getValue(), 0, 11);
            $contract['contact_person'] = $sheet->getCell('A' . $i)->getValue();

            $contract['corporate_name'] = $sheet->getCell('A' . $i)->getValue();

            $contract['installed_time'] = strtotime($sheet->getCell('M' . $i)->getValue()) ? strtotime($sheet->getCell('M' . $i)->getValue()) : time();
            $contract['address'] = $sheet->getCell('G' . $i)->getValue();
            $contract['for_core_cycle'] = $this->findNum($sheet->getCell('N' . $i)->getValue());
            $contract['recent_time'] = strtotime($sheet->getCell('P' . $i)->getValue()) ? strtotime($sheet->getCell('P' . $i)->getValue()) : time();

            $contract['for_core_time'] = strtotime('+' . $contract['for_core_cycle'] . 'month', $contract['recent_time']);
//            $contract['for_core_cost'] = explode('元', $sheet->getCell('L' . $i)->getValue())[0] ?? 0;
            $contract['for_core_cost'] = explode('元', $sheet->getCell('L' . $i)->getValue())[0];

//            $contract['renew_date'] = $sheet->getCell('M' . $i)->getValue() ?? 1;
            $contract['renew_date'] = 1;
            $contract['exire_date'] = strtotime("+1year", strtotime($sheet->getCell('R' . $i)->getValue()));

            $contract['nature'] = $nature;
            $contract['housing_property'] = $housing_property;
            $contract['contract_type'] = $user_type - 1;
            $contract['is_explain'] = ($sheet->getCell('T' . $i)->getValue() == '无') ? 2 : 1;


            //合同备注
            $contract_remark['contract_no'] = trim($sheet->getCell('C' . $i)->getValue());
            $contract_remark['remark'] = $sheet->getCell('V' . $i)->getValue();



            //合同设备
            $c_eq['contract_id'] = $c_id;
            $c_eq['equipments_id'] = $this->getEq(trim($sheet->getCell('K' . $i)->getValue()));
            $c_eq['equipments_number'] = randEquipmentsNumber();
            $c_eq['equipments_address'] = $sheet->getCell('G' . $i)->getValue();



            //合同日志
            $Log = [
                'contract_id' => $c_id,
                'do_type' => 1,
                'terminal_type' => 1,
                'new_value' => '合同，合同编号：' . $contract_remark['contract_no'],
            ];


            if (in_array($u_data['username'],session('username')) || in_array($contract['contract_no'],session('contract_no'))){
                if (in_array($u_data['username'],session('username'))){
                    array_push($error_user,$u_data);
                }
                if (in_array($contract['contract_no'],session('contract_no'))){
                    array_push($error,$contract);
                }
            }else{
                array_push($user_data, $u_data);
                array_push($user_a_data, $u_a_data);
                array_push($contract_data, $contract);
                array_push($contract_remark_data, $contract_remark);
                array_push($c_eq_data, $c_eq);
                array_push($c_log, $Log);
            }

//            array_push($user_data, $u_data);
//            array_push($user_a_data, $u_a_data);
//            array_push($contract_data, $contract);
//            array_push($contract_remark_data, $contract_remark);
//            array_push($c_eq_data, $c_eq);
//            array_push($c_log, $Log);
//            break;
            $user_id++;
            $c_id++;
        }


//        dump($error_user);
//        dump($error);
//        dump(count($user_data));
//        dump(count($user_a_data));
//        dump(count($contract_data));
//        dump(count($contract_remark_data));
//        dump(count($c_eq_data));
//        dump(count($c_log));

//        dump($contract_remark_data);
//        dump($c_eq_data);
//        dump($contract_data);
//        exit();
        $tmp_user_name = [];
        foreach ($user_data as $uk=>$uv){
            if (in_array($uv['username'],$tmp_user_name)){
                unset($user_data[$uk]);
            }else{
                array_push($tmp_user_name,$uv['username']);
            }

        }
        Db::startTrans();
        try {
            Db::table('rq_contract')->insertAll($contract_data);
            Db::table('rq_customer')->insertAll($user_data);
            Db::table('rq_customer_administrative_center')->insertAll($user_a_data);
            Db::table('rq_contract_remark')->insertAll($contract_remark_data);
            Db::table('rq_contract_equipment')->insertAll($c_eq_data);
            Db::table('rq_contract_log')->insertAll($c_log);
            // 提交事务
            echo "--------------------------<br>";
            echo date('Y-m-d H:i:s',time())."<br>";
            echo "导入成功<br>";
            echo "成功导入客户：".count($user_data).'<br>';
            echo "成功导入合同：".count($contract_data).'<br>';
            echo "用户失败条数：".count($error_user).'<br>';
            echo '合同失败条数：'.count($error).'<br>';

            if (!empty($error_user)){
                $tmp_error_user = \session('error_user');
                \session('error_user',array_merge($tmp_error_user,$error_user));
            }
            if (!empty($error)){
                $tmp_error = \session('error');
                \session('error',array_merge($tmp_error,$error));
            }

            Db::commit();
//            $this->people();

//            session('user_id', $user_id);
//            session('c_id', $c_id);

        } catch (\Exception $e) {
            echo $e;
            // 回滚事务
            Db::rollback();
        }
        $this->people();
    }

    protected function findNum($str = '')
    {
        $str = trim($str);
        if (empty($str)) {
            return '';
        }
        $result = '';
        for ($i = 0; $i < strlen($str); $i++) {
            if (is_numeric($str[$i])) {
                $result .= $str[$i];
            }
        }
        return $result;
    }

    /**
     * @author ligang
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @date 2018/9/10 15:22
     */
    public function ligang()
    {

        $time = time();
        ini_set("max_execution_time", "0");
        header("Content-type:text/html;charset=utf-8");

        $where = [
            'work_order_status'=>12,
            'is_delete'=>0
        ];
        $field = '
            rq_work_order.work_order_id,
            rq_work_order.order_number,
            rq_work_order.contract_id,
            rq_work_order.business_id,
            rq_work_order.repair_id,
            rq_work_order.user_id
        ';
        $order = Db::table('rq_work_order')->where($where)->field($field)->order(['order_time'=>'ASC'])->select();
        $f_field = '
            rq_contract.contract_id,
            rq_contract.user_id,
            rq_contract.introducer_id,
            rq_contract.contract_no,
            rq_contract.applicant,
            rq_contract.nature,
            rq_contract.eq_count,
            rq_contract.is_explain,
            rq_contract.is_special
        ';
        $contract = Db::table('rq_contract')->field($f_field)->order(['add_time'=>'ASC'])->select();

        $device = Db::table('rq_contract_equipment')->where(['state'=>1,'is_delete'=>0])->field('id,contract_id')->order(['addtime'=>'ASC'])->select();
        $u_field = '
            rq_customer.user_id,
            rq_customer.username,
            rq_customer.id_card,
            rq_customer.employee_id,
            rq_customer.is_entry
        ';
        $user = Db::table('rq_customer')->field($u_field)->select();

        $repair = Db::table('rq_engineers')->field('engineers_id,engineers_name,engineers_number')->select();

        //
        $data = [];
        foreach ($order as $key=>$value){
            foreach ($contract as $k=>$v){
                if ($value['contract_id'] == $v['contract_id']){
                    $tmp = [
                        'is_explain'=>$v['is_explain'],
                        'achievement_work_order_number'=>$value['order_number'],
                        'achievement_work_order_type'=>$value['business_id'],
                        'engineers_name'=>'',
                        'engineers_number'=>'',
                        'contract_number'=>$v['contract_no'],
                        'nature'=>$v['nature'],
                        'contract_customer'=>'',
                        'contract_customer_id_card'=>'',
                        'contract_introducer'=>'',
                        'applicant'=>'',
                        'equipments_number'=>($v['nature'] == 1) ? $v['eq_count'] : '',
                        'request_time'=>time(),
                    ];
                    //工程人员
                    foreach ($repair as $kk=>$vv){
                        if ($vv['engineers_id'] == $value['repair_id']){
                            $tmp['engineers_name'] = $vv['engineers_name'];
                            $tmp['engineers_number'] = $vv['engineers_number'];
                            break;
                        }
                    }
                    //合同客户
                    foreach ($user as $kkk=>$vvv){
                        if ($vvv['user_id'] == $v['user_id']){
                            $tmp['contract_customer'] = $vvv['username'];
                            $tmp['contract_customer_id_card'] = $vvv['id_card'];
                            break;
                        }
                    }
                    //合同转介绍
                    if (!empty($v['introducer_id'])){
                        foreach ($user as $kkkk=>$vvvv){
                            if ($vvvv['user_id'] == $v['introducer_id']){
                                $tmp['contract_introducer'] = $vvvv['username'];//在业绩系统中制作标识，不计算不储存
                                if ($vvvv['is_entry'] == 1){
                                    $tmp['applicant'] = $vvvv['employee_id'];
                                }
                                break;
                            }
                        }
                    }
                    //申请人
                    if (empty($tmp['applicant'])){
                        foreach ($user as $ak=>$av){
                            if ($av['user_id'] == $v['applicant']){
                                $tmp['applicant'] = $av['employee_id'];
                                break;
                            }
                        }
                    }

                    if (empty($tmp['equipments_number'])){
                        $number = 0;
                        foreach ($device as $dk=>$dv){
                            if ($dv['contract_id'] == $v['contract_id']){
                                $number += 1;
                            }
                        }
                    }else{
                        array_push($data,$tmp);
                        break;
                    }

                    if (isset($number) && $number != 0){
                        $tmp['equipments_number'] = $number;
                        array_push($data,$tmp);
                        break;
                    }
                    dump([
                        'order_number'=>$value['order_number'],
                        'contract_no'=>$v['contract_no'],
                        'note'=>'合同工单不存在设备台数'
                    ]);
                }
            }
        }


//        dump($data);
//        dump(var_export($data,true));

        $file_path = ROOT_PATH.'public'.DS.'customer'.DS.'new'.DS.'order.php';

        $content = '<?php ';
        $content .= '$data='.var_export($data,true).'; ';
        $content .= 'return $data;';
        $diff_time = time() - $time;
//        dump($diff_time);
//        dump($content);
//        exit();
        file_put_contents($file_path,$content);
//        dump(file_exists($file_path));

    }

    protected function excelTime($date, $time = false)
    {
        if (function_exists('GregorianToJD')) {
            if (is_numeric($date)) {
                $jd = GregorianToJD(1, 1, 1970);
                $gregorian = JDToGregorian($jd + intval($date) - 25569);
                $date = explode('/', $gregorian);
                $date_str = str_pad($date [2], 4, '0', STR_PAD_LEFT)
                    . "-" . str_pad($date [0], 2, '0', STR_PAD_LEFT)
                    . "-" . str_pad($date [1], 2, '0', STR_PAD_LEFT)
                    . ($time ? " 00:00:00" : '');
                return $date_str;
            }
        } else {
            $date = $date > 25568 ? $date + 1 : 25569;
            /*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
            $ofs = (70 * 365 + 17 + 2) * 86400;
            $date = date("Y-m-d", ($date * 86400) - $ofs) . ($time ? " 00:00:00" : '');
        }
        return $date;
    }

    /**
     * 导入业务员信息（排查重复数据）
     * @author ligang
     * @date 2018/5/17 11:18
     */
    public function excelImportSalesman()
    {
        return false;
        /*
         *   导入思路：
         *          1.若果数据量太大考虑，分页导入
         *          2.员工所属的运营中心
         *          3.员工的员工编号（编号表）
         *          3.员工行政中心关联表
         */
        ob_end_clean();
        set_time_limit(0);
        ini_set('memory_limit', '3072M');
        ini_set("max_execution_time", "0");
        $file = ROOT_PATH . 'public' . DS . 'customer' . DS . 'Salesman_3.xlsx';
        // 判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type = $type === 'Excel5' ? $type : 'Excel2007';

        Vendor('phpexcel.phpexcel.Classes.PHPExcel');
        // 判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);
        $sheet = $objPHPExcel->getSheet(0);
        // 取得总行数
        $highestRow = $sheet->getHighestRow();

        // 取得总列数
        $highestColumn = $sheet->getHighestColumn();

        //用户数据
        $user_data = [
            'telphone' => '',//联系电话
            'id_card' => '',//身份证号码
            'username' => '',//客户姓名
            'avatar_img' => 0,//用户头像地址
            'province' => '重庆市',//省
            'city' => '重庆市',//市
            'area' => '渝中区',//区
            'province_code' => 23,//省ID
            'city_code' => 271,//市ID
            'area_code' => 2494,//区ID
            'number' => '',//当前推荐有效设备数（直推设备台数）
            'team_number' => '',//团队推荐台数（含直推）
            'user_address' => '',//客户详细地址
            'source' => 0,//客户来源(联系人ID)
            'birthday' => '',//生日
            'operation_id' => 1,//运营中心ID
            'nature' => 2,//客户性质(1.商用客户,2.家用客户,3外接包年,4.外接点餐,5.购买客户)
            'housing_property' => 4,//住房性质(1.公租房,2.廉租房,3.租赁,4.购买)
            'is_entry' => 1,//是否入职(1.入职，3.离职，2.客户)
            'user_type' => 1,//用户类型(1.普通用户,2.vip用户) 商用客户or家用客户必选
            'remark' => '',//帐号备注
            'employee_id' => '',//员工编号
            'add_time' => time(),//注册时间
        ];

        $user_number_data = [
            'personnel_number' => '',//100001开始
            'personnel_time' => '',
        ];

        $user_a_data = [
            'user_id' => '',
            'administrative_id' => 32
        ];
        //循环读取excel文件,读取一条,插入一条
        $data = [];
        $p_data = [];
        $a_data = [];
        //从第一行开始读取数据
        $n = 102500;
        $k = 2500;

        /*
        $id = 1;
        */
        for ($j = 3; $j <= 78; $j++) {
            $user_a_data['user_id'] = $k;
            $user_number_data['personnel_number'] = $n;
            $user_number_data['personnel_time'] = strtotime($this->excelTime($objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue())) ? strtotime($this->excelTime($objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue())) : time();
            $user_data['username'] = $objPHPExcel->getActiveSheet()->getCell('B' . $j)->getValue();
            $user_data['telphone'] = substr($objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue(), 0, 11);
            $user_data['user_address'] = empty($objPHPExcel->getActiveSheet()->getCell('J' . $j)->getValue()) ? '详细地址' : $objPHPExcel->getActiveSheet()->getCell('J' . $j)->getValue();
            $user_data['id_card'] = empty($objPHPExcel->getActiveSheet()->getCell('G' . $j)->getValue()) ? '000000000000000000' : $objPHPExcel->getActiveSheet()->getCell('G' . $j)->getValue();
            $user_data['birthday'] = strtotime(substr($user_data['id_card'], 6, 4) . '/' . substr($user_data['id_card'], 10, 2) . '/' . substr($user_data['id_card'], 12, 2));
            $user_data['employee_id'] = $n;
            $user_data['remark'] = substr($objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue(), 11);
            array_push($data, $user_data);
            array_push($p_data, $user_number_data);
            array_push($a_data, $user_a_data);
            $n++;
            $k++;

            /*
            try{
                $tel = substr($objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue(),0,11);
                $remark = substr($objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue(),11);
                Db::name('customer')->where(['user_id'=>$id])->update(['telphone'=>$tel,'remark'=>$remark]);
                $id ++;
            }catch (\Exception $e){
                echo $e."<br>";
                dump($id);
                return false;
                exit();
            }
            */

        }
        Db::startTrans();
        try {
            Db::table('rq_customer')->insertAll($data);
            Db::table('rq_customer_administrative_center')->insertAll($a_data);
            Db::table('rq_personnel_number')->insertAll($p_data);
            // 提交事务
            echo "成功";
            Db::commit();
        } catch (\Exception $e) {
            echo $e;
            // 回滚事务
            Db::rollback();
        }


    }

    public function test()
    {
        return false;
//        exit();
//        ob_end_clean();//清除缓冲区,避免乱码
        set_time_limit(0);
        ini_set('memory_limit', '3072M');
        ini_set("max_execution_time", "0");
        ob_end_clean();//清除缓冲区,避免乱码
        ob_start();
        header('Content-Type:text/html;charset=utf-8');
        $key = input('get.ligang/d');
        if ($key != '0523') {
            $this->error('私人订制');
        }
        try {
            $field = '
                rq_customer.user_id,
                rq_customer.telphone,
                rq_customer.id_card,
                rq_customer.username,
                rq_customer.employee_id,
                rq_customer.birthday,
                rq_customer.user_address,
                rq_customer.housing_property,
                rq_customer.nature,
                rq_customer.is_entry,
                rq_customer.remark,
                rq_customer.user_type
            ';
            $data = Db::name('customer')->where(['user_id' => ['>', 2499]])->field($field)->select();

            $filename = '用户信息表' . '-' . date('Y-m-d H:i:s', time()) . ".xls";
            Vendor('phpexcel.phpexcel.Classes.PHPExcel');
            $PHPExcel = new \PHPExcel();
            //设置信任
            $PHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");
            //设置表头
            $PHPExcel->getActiveSheet()->setCellValue('A1', '客户姓名');
            $PHPExcel->getActiveSheet()->setCellValue('B1', '身份证');
            $PHPExcel->getActiveSheet()->setCellValue('C1', '联系电话');
            $PHPExcel->getActiveSheet()->setCellValue('D1', '是否入职');
            $PHPExcel->getActiveSheet()->setCellValue('E1', '员工编号');
            $PHPExcel->getActiveSheet()->setCellValue('F1', '客户性质');
            $PHPExcel->getActiveSheet()->setCellValue('G1', '住房性质');
            $PHPExcel->getActiveSheet()->setCellValue('H1', '用户类型');
            $PHPExcel->getActiveSheet()->setCellValue('I1', '生日');
            $PHPExcel->getActiveSheet()->setCellValue('J1', '客户详细地址');
            $PHPExcel->getActiveSheet()->setCellValue('K1', '帐号备注');
            //设置列宽
            $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(28);
            $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(28);
            //设置字体样式
            $PHPExcel->getActiveSheet()->getStyle('A1:K1')->getFont()->setName('微软雅黑');
            $PHPExcel->getActiveSheet()->getStyle('A1:K1')->getFont()->setSize(14);
            $PHPExcel->getActiveSheet()->getStyle('A1:K1')->getFont()->setBold(true);


            foreach ($data as $k => $v) {
                switch ($v['nature']) {
                    case 1:
                        $nature = '商用客户';
                        break;
                    case 2:
                        $nature = '家用客户';
                        break;
                    case 3:
                        $nature = '外接包年';
                        break;
                    case 4:
                        $nature = '外接点餐';
                        break;
                    default:
                        $nature = '购买客户';
                }
                switch ($v['housing_property']) {
                    case 1:
                        $housing_property = '公租房';
                        break;
                    case 2:
                        $housing_property = '廉租房';
                        break;
                    case 3:
                        $housing_property = '租赁';
                        break;
                    default:
                        $housing_property = '购买';
                }
                //设置单元格值
                $PHPExcel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['username']);
                $PHPExcel->getActiveSheet()->setCellValue('B' . ($k + 2), ' ' . $v["id_card"]);
                $PHPExcel->getActiveSheet()->setCellValue('C' . ($k + 2), $v['telphone']);
                $PHPExcel->getActiveSheet()->setCellValue('D' . ($k + 2), ($v['is_entry'] == 1) ? '是' : '否');
                $PHPExcel->getActiveSheet()->setCellValue('E' . ($k + 2), $v['employee_id']);
                $PHPExcel->getActiveSheet()->setCellValue('F' . ($k + 2), $nature);
                $PHPExcel->getActiveSheet()->setCellValue('G' . ($k + 2), $housing_property);
                $PHPExcel->getActiveSheet()->setCellValue('H' . ($k + 2), $v['user_type'] == 1 ? '普通用户' : 'vip用户');
                $PHPExcel->getActiveSheet()->setCellValue('I' . ($k + 2), date('Y-m-d', $v['birthday']));
                $PHPExcel->getActiveSheet()->setCellValue('J' . ($k + 2), $v['user_address']);
                $PHPExcel->getActiveSheet()->setCellValue('K' . ($k + 2), $v['remark']);


                //设置居中
                $PHPExcel->getActiveSheet()->getStyle('A1:K' . ($k + 2))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //垂直居中
                $PHPExcel->getActiveSheet()->getStyle('A' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('B' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('C' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('D' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('E' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('F' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('G' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('H' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('I' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('J' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('K' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            $PHPSheet = $PHPExcel->getActiveSheet();
            //给当前活动sheet设置名称
            $PHPSheet->setTitle("用户信息");
            //创建生成的格式
            $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel5");
            //下载下来的表格名
            ob_end_clean();
            header('content-Type:application/vnd.ms-excel;charset=utf-8');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Cache-Control: max-age=0');
            $PHPWriter->save("php://output");
        } catch (\Exception $e) {
            echo $e;
        }

    }

    /**
     *  导出业务员信息
     * @author ligang
     * @date 2018/5/17 16:07
     */
    public function exportSalesman()
    {
        return false;
        ini_set("max_execution_time", "0");
        ob_end_clean();//清除缓冲区,避免乱码
        ob_start();
        header('Content-Type:text/html;charset=utf-8');
        try {
            $join = [
                ['rq_customer_administrative_center', 'rq_customer.user_id = rq_customer_administrative_center.user_id', 'LEFT'],
                ['rq_administrative_info', 'rq_customer_administrative_center.administrative_id = rq_administrative_info.a_id', 'LEFT'],
                ['rq_operation_info', 'rq_customer.operation_id = rq_operation_info.o_id', 'LEFT']
            ];
            $field = '
                rq_operation_info.company_name as o_company_name,
                rq_administrative_info.company_name as a_company_name,
                rq_customer.user_id,
                rq_customer.telphone,
                rq_customer.id_card,
                rq_customer.username,
                rq_customer.employee_id,
                rq_customer.birthday,
                rq_customer.user_address,
                rq_customer.housing_property,
                rq_customer.nature,
                rq_customer.is_entry,
                rq_customer.remark,
                rq_customer.user_type
            ';
//            $data = Db::name('customer')->field($field)->join($join)->select();

            $repeat_people = Db::name('customer')->field('rq_customer.username,Count(*) AS count')->group('username')->having('count>1')->select();
            $data = [];
            foreach ($repeat_people as $key=>$value){
                $repeat_people_info = Db::name('customer')->where(['username'=>$value['username']])->select();
                foreach ($repeat_people_info as $k=>$v){
                    array_push($data,$v);
                }
            }

            $filename = '用户信息表' . '-' . date('Y-m-d H:i:s', time()) . ".xls";
            Vendor('phpexcel.phpexcel.Classes.PHPExcel');
            $PHPExcel = new \PHPExcel();
            //设置信任
            $PHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");
            //设置表头
            $PHPExcel->getActiveSheet()->setCellValue('A1', '用户ID');
            $PHPExcel->getActiveSheet()->setCellValue('B1', '客户姓名');
            $PHPExcel->getActiveSheet()->setCellValue('C1', '身份证');
            $PHPExcel->getActiveSheet()->setCellValue('D1', '联系电话');
            $PHPExcel->getActiveSheet()->setCellValue('E1', '是否入职');
            $PHPExcel->getActiveSheet()->setCellValue('F1', '员工编号');
            $PHPExcel->getActiveSheet()->setCellValue('G1', '客户性质');
            $PHPExcel->getActiveSheet()->setCellValue('H1', '住房性质');
            $PHPExcel->getActiveSheet()->setCellValue('I1', '用户类型');
            $PHPExcel->getActiveSheet()->setCellValue('J1', '生日');
            $PHPExcel->getActiveSheet()->setCellValue('K1', '客户详细地址');
            $PHPExcel->getActiveSheet()->setCellValue('L1', '帐号备注');

            //设置列宽
            $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
            $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(28);
            $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
            $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
            $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(14);
            $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(14);
            $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14);
            $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
            $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(40);
            $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
            //设置字体样式
            $PHPExcel->getActiveSheet()->getStyle('A1:L1')->getFont()->setName('微软雅黑');
            $PHPExcel->getActiveSheet()->getStyle('A1:L1')->getFont()->setSize(14);
            $PHPExcel->getActiveSheet()->getStyle('A1:L1')->getFont()->setBold(true);


            foreach ($data as $k => $v) {
                switch ($v['nature']) {
                    case 1:
                        $nature = '商用客户';
                        break;
                    case 2:
                        $nature = '家用客户';
                        break;
                    case 3:
                        $nature = '外接包年';
                        break;
                    case 4:
                        $nature = '外接点餐';
                        break;
                    default:
                        $nature = '购买客户';
                }
                switch ($v['housing_property']) {
                    case 1:
                        $housing_property = '公租房';
                        break;
                    case 2:
                        $housing_property = '廉租房';
                        break;
                    case 3:
                        $housing_property = '租赁';
                        break;
                    default:
                        $housing_property = '购买';
                }
                //设置单元格值
                $PHPExcel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['user_id']);
                $PHPExcel->getActiveSheet()->setCellValue('B' . ($k + 2), $v['username']);
                $PHPExcel->getActiveSheet()->setCellValue('C' . ($k + 2), ' '.$v["id_card"]);
                $PHPExcel->getActiveSheet()->setCellValue('D' . ($k + 2), $v['telphone']);
                $PHPExcel->getActiveSheet()->setCellValue('E' . ($k + 2), ($v['is_entry'] == 1) ? '是' : '否');
                $PHPExcel->getActiveSheet()->setCellValue('F' . ($k + 2), $v['employee_id']);
                $PHPExcel->getActiveSheet()->setCellValue('G' . ($k + 2), $nature);
                $PHPExcel->getActiveSheet()->setCellValue('H' . ($k + 2), $housing_property);
                $PHPExcel->getActiveSheet()->setCellValue('I' . ($k + 2), $v['user_type'] == 1 ? '普通用户' : 'vip用户');
                $PHPExcel->getActiveSheet()->setCellValue('J' . ($k + 2), date('Y-m-d', $v['birthday']));
                $PHPExcel->getActiveSheet()->setCellValue('K' . ($k + 2), $v['user_address']);
                $PHPExcel->getActiveSheet()->setCellValue('L' . ($k + 2), $v['remark']);


                //设置居中
                $PHPExcel->getActiveSheet()->getStyle('A1:L' . ($k + 2))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //垂直居中
                $PHPExcel->getActiveSheet()->getStyle('A' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('B' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('C' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('D' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('E' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('F' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('G' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('H' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('I' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('J' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('K' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $PHPExcel->getActiveSheet()->getStyle('L' . $k)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            $PHPSheet = $PHPExcel->getActiveSheet();
            //给当前活动sheet设置名称
            $PHPSheet->setTitle("用户信息");
            //创建生成的格式
            $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel5");
            //下载下来的表格名
            ob_end_clean();
            header('content-Type:application/vnd.ms-excel;charset=utf-8');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Cache-Control: max-age=0');
            $PHPWriter->save("php://output");
        } catch (\Exception $e) {
            echo $e;
        }

    }

    /**
     * 获取运营中心的ID和行政中心的ID
     * @author ligang
     * @param $province
     * @param $city
     * @param $area
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @date 2018/5/18 14:32
     */
    protected function getRegion($province, $city, $area)
    {
        $where = [
            'province_name' => $province,
            'city_name' => $city,
            'area_name' => ['LIKE', '%' . $area . '%'],
        ];
        Db::name('administrative_info')->where($where)->find();
    }

    protected function getAdministrative($name)
    {
        if ($name == '总公司' || $name == '总部') {
            $name = '重庆润泉水处理有限公司';
        }
        $where = [
            'company_name' => ['LIKE', '%' . $name . '%']
        ];
        $result = Db::name('administrative_info')->where($where)->field('*')->find();
//        return $result ?? '';
        return $result;

    }

    protected function getEq($name)
    {
        $result = Db::name('equipments')->where(['equipments_name' => $name])->field('equipments_id')->find();
//        return $result['equipments_id'] ?? '';
        return isset($result['equipments_id'])?$result['equipments_id']:0;
    }

    protected function getWorker($num)
    {
        $result = Db::name('customer')->where(['employee_id' => $num])->field('user_id')->find();
//        return $result['user_id'] ?? '';
        return isset($result['user_id'])?$result['user_id']:'';
    }

    protected function getContract($num)
    {
        if (empty($num)){
            return '';
        }
        $result = Db::name('contract')->where(['contract_no' => $num])->field('user_id')->find();
//        return $result['user_id'] ?? '';

        return isset($result['user_id'])?$result['user_id']:0;
    }
}


